package com.ruoyi.certificate.mapper;

import com.ruoyi.certificate.domain.CertificateIncreaseDo;
import com.ruoyi.certificate.domain.CertificateRankDo;
import com.ruoyi.certificate.domain.GameTypeYearDo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

/**
 * @author love ice
 * @create 2023-12-20 17:47
 */
@Mapper
public interface ZnlCertificateInfoEchartsMapper {

    @Select("SELECT YEAR\n" +
            "\t( acquisition_time ) AS year,\n" +
            "\tSUM( CASE WHEN game_type = '1' THEN 1 ELSE 0 END ) AS nationalCount,\n" +
            "\tSUM( CASE WHEN game_type = '2' THEN 1 ELSE 0 END ) AS provinceCount,\n" +
            "\tSUM( CASE WHEN game_type = '3' THEN 1 ELSE 0 END ) AS cityCount,\n" +
            "\tSUM( CASE WHEN game_type = '4' THEN 1 ELSE 0 END ) AS schoolCount \n" +
            "FROM\n" +
            "\tznl_certificate_info \n" +
            "GROUP BY\n" +
            "\tyear")
    List<GameTypeYearDo> gameTypeYear();



        @Select("SELECT DATE(acquisition_time) AS acquisitionDate, certificate_type AS certificateType, COUNT(*) AS count " +
            "FROM znl_certificate_info " +
            "WHERE acquisition_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY acquisitionDate, certificateType;")
    List<CertificateIncreaseDo> getCertificateInfoByDate(@Param("startDate") LocalDate startDate,
                                                         @Param("endDate") LocalDate endDate);


    @Select("SELECT certificate_rank as dateKey, COUNT(*) as count " +
            "FROM znl_certificate_info " +
            "WHERE YEAR(acquisition_time) = #{year} " +
            "GROUP BY certificate_rank")
    List<CertificateRankDo> getCountByCertificateRank(@Param("year") int year);


    @Select("SELECT certificate_type as dateKey, COUNT(*) as count " +
            "FROM znl_certificate_info " +
            "WHERE YEAR(acquisition_time) = #{year} " +
            "GROUP BY certificate_rank")
    List<CertificateRankDo> getCountByCertificateType(@Param("year") int year);

    @Select("SELECT game_type as dateKey, COUNT(*) as count " +
            "FROM znl_certificate_info " +
            "WHERE YEAR(acquisition_time) = #{year} " +
            "GROUP BY certificate_rank")
    List<CertificateRankDo> getGameType(@Param("year") int year);
}
